SET SERVEROUTPUT ON

CREATE or replace FUNCTION get_filesize(p_file_ptr IN OUT BFILE)
RETURN NUMBER IS
  v_file_exists BOOLEAN;
  v_length NUMBER:= -1;
BEGIN
  v_file_exists := DBMS_LOB.FILEEXISTS(p_file_ptr) = 1;
  IF v_file_exists THEN
    DBMS_LOB.FILEOPEN(p_file_ptr);
    v_length := DBMS_LOB.GETLENGTH(p_file_ptr);
    DBMS_LOB.FILECLOSE(p_file_ptr);
  END IF;
  RETURN v_length;
END;
/

DECLARE
  v_fp BFILE := BFILENAME('DATA_FILES', 'Alice.gif');
BEGIN
  dbms_output.put_line(get_filesize(v_fp));
END;
/